package com.yidu.oracle.dao;

import com.yidu.oracle.Tools.DBUtil;
import oracle.jdbc.OracleTypes;

import java.sql.*;

/**
 * @ClassName wjy
 * @Description
 * @Author86157
 * @Date 2021/3/19 11:03
 * @Version 1.0
 */
public class OracleTest {
    /**
     *  调用函数，返回6位数密码
     */
    public static void functionTest(){
        //数据库
        Connection conn = null;
        CallableStatement call = null;
        try {
            conn = DBUtil.getConnection();
            String sql="{?=call func_emp()}";
            call=conn.prepareCall(sql);
            //注册输出参数和返回值
            call.registerOutParameter(1, Types.VARCHAR);
            //执行
            call.execute();
            //获取输出参数和返回值
            String test1 = call.getString(1);
            System.out.println(test1);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     *  jdbc中调用返回游标的函数，在控制台中打印输出信息
     * @param
     */
    public static void fun_cursor(){
        Connection conn = null;
        CallableStatement call = null;

        try {
            conn =  DBUtil.getConnection();
            String sql="{?=call func_cursor()}";
            call=conn.prepareCall(sql);
            //注册返回值
            call.registerOutParameter(1, OracleTypes.CURSOR);
            //执行
            call.execute();
            //获取返回的游标 游标就是结果集
            ResultSet rs = (ResultSet) call.getObject(1);
            //循环打印
            while(rs.next()){
                System.out.println("员工编号:"+rs.getInt("EMPNO")+"员工姓名:"+rs.getString("ENAME")
                        +"员工职位:"+rs.getString("JOB")+"员工mgr"+rs.getString("MGR")+"员工出生年份："+rs.getString("HIREDATE")+
                        "员工薪资:"+rs.getInt("SAL")+"员工奖金："+rs.getInt("COMM")+"员工部门编号:"+rs.getInt("DEPTNO"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //--4.根据部门编号查询员工信息，定义存储过程，输出游标，通过jdbc中获取返回游标控制台打印输出
    public static void procEmpno(){
        Connection conn= null;
        CallableStatement call =null;
        try {
            conn= DBUtil.getConnection();
            String sql="{call proc_Empno(?,?)}";
            call=conn.prepareCall(sql);
            //输入值
            call.setInt(1,30);
            //注册输出值
            call.registerOutParameter(2,OracleTypes.CURSOR);
            //执行
            call.execute();
            //获取输出参数游标 游标就是结果集
            ResultSet rs = (ResultSet) call.getObject(2);
            //输出参数
            while(rs.next()){
                System.out.println("员工编号:"+rs.getInt("EMPNO")+"员工姓名:"+rs.getString("ENAME")
                        +"员工职位:"+rs.getString("JOB")+"员工mgr"+rs.getString("MGR")+"员工出生年份："+rs.getString("HIREDATE")+
                        "员工薪资:"+rs.getInt("SAL")+"员工奖金："+rs.getInt("COMM")+"员工部门编号:"+rs.getInt("DEPTNO"));

            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static void main(String[] args) {
        new OracleTest().functionTest();
    }
}
